package student;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @Author admin
 * @Date 2019/5/5 8:23
 */
public class StudentDaoDB implements StudentDao {
    Connection conn = JDBCUtil.getConn();
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    @Override
    public int insert(Student student) {
        Student s = selectById(student.getId());

        if (s == null) {
            String sql = "insert into student(id,name,sex,age,className) values(?,?,?,?,?)";
            try {
                pstmt = conn.prepareStatement(sql);
                pstmt.setString(1, student.getId());
                pstmt.setString(2, student.getName());
                pstmt.setString(3, student.getSex());
                pstmt.setInt(4, student.getAge());
                pstmt.setString(5, student.getClassName());

                int c = pstmt.executeUpdate();
                return c;
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public ArrayList<Student> selectAll() {
        ArrayList<Student> arrayList = new ArrayList<Student>();
        String sql = "select * from student";
        try {
            /*获得执行SQL语句的pstmt对象*/
            pstmt = conn.prepareStatement(sql);
            /*执行查询语句，结果保存在rs*/
            rs = pstmt.executeQuery();
            /*将结果集中的内容保存在集合*/

            while (rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String sex = rs.getString("sex");
                int age = rs.getInt("age");
                String className = rs.getString("className");
                Student student = new Student(id, name, sex, age, className);
                arrayList.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return arrayList;
    }

    @Override
    public Student selectById(String id) {
        Student student = null;
        String sql = "select * from student where id=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, id);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                String name = rs.getString("name");
                String sex = rs.getString("sex");
                int age = rs.getInt("age");
                String className = rs.getString("className");
                student = new Student(id, name, sex, age, className);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return student;
    }

    @Override
    public int update(Student student) {
        String sql = "update student set name=?,sex=?,age=?,className=?" + "where id =?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, student.getName());
            pstmt.setString(2, student.getSex());
            pstmt.setInt(3, student.getAge());
            pstmt.setString(4, student.getClassName());
            pstmt.setString(5, student.getId());
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public int delete(String id) {
        String sql = "delete from student where id =?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,id);
            int c=pstmt.executeUpdate();
            return c;
        } catch (SQLException e) {
            e.printStackTrace();
        }


        return 0;
    }

    @Override
    public void close() {
        JDBCUtil.close(rs, pstmt, conn);
    }
}
